package com.ncloud.act.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.alibaba.fastjson.JSONObject;
import com.ncloud.act.entity.ChnlAdjustBill;

@Mapper
public interface ChnlAdjustBillMapper{
	@Insert(value = "INSERT INTO sys_chnl_adjustbill (adjust_id,total_id, chnl_id, chnl_name, bill_cycle_id,adjust_cycle_id,adjust_type, adjust_fee, adjust_balance, pay_tag, create_time, create_user_id, update_time, update_user_id, remark)  "
									+ "values(#{adjust_id},#{total_id},#{chnl_id},#{chnl_name},#{bill_cycle_id},#{adjust_cycle_id},#{adjust_type},#{adjust_fee},#{adjust_balance},#{pay_tag},#{create_time},#{create_user_id},#{update_time},#{update_user_id},#{remark})")
    public int insertChnlAdjustBill(ChnlAdjustBill chnlAdjustBill);
	
	@Update(value = "UPDATE sys_chnl_adjustbill SET adjust_balance = #{adjust_balance}, pay_tag = #{pay_tag}, update_time = #{update_time}, update_user_id = #{update_user_id} WHERE adjust_id = #{adjust_id} ")
    public int updateChnlAdjustBill(ChnlAdjustBill chnlAdjustBill);
	
	@Select("select ifnull(sum(ifnull(adjust_fee,0)),0) as adjust_fee from sys_chnl_adjustbill "
			+ "where chnl_id=#{0} and bill_cycle_id<#{1} and pay_tag=#{2} ")
	@Results({@Result(column="adjust_fee",property="adjust_fee")})
	List<JSONObject> getAdjustFeeByChnlID(Long chnl_id,Integer bill_cycle_id,String pay_tag);
	
	@Select("SELECT adjust_id, total_id, chnl_id, chnl_name, bill_cycle_id, adjust_cycle_id, adjust_type, adjust_fee, adjust_balance, "
			+ "pay_tag, date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_adjustbill "
			+ "where chnl_id=#{0} and pay_tag=#{1} order by adjust_balance,bill_cycle_id ")
	@Results({@Result(column="adjust_id",property="adjust_id"),@Result(column="total_id",property="total_id"),
		@Result(column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="bill_cycle_id",property="bill_cycle_id"),@Result(column="adjust_cycle_id",property="adjust_cycle_id"),
		@Result(column="adjust_type",property="adjust_type"),@Result(column="adjust_fee",property="adjust_fee"),
		@Result(column="adjust_balance",property="adjust_balance"),@Result(column="pay_tag",property="pay_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlAdjustBill> getAdjustBillByChnlID(Long chnl_id,String pay_tag);
	@Select("SELECT adjust_id, total_id, chnl_id, chnl_name, bill_cycle_id, adjust_cycle_id, adjust_type, adjust_fee, adjust_balance, "
			+ "pay_tag, date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_adjustbill "
			+ "where create_time >=#{1} and create_time <=#{2} "
			+ "and FIND_IN_SET(chnl_id,f_getChnlList(#{0})) "
			+ "order by create_time desc ")
	@Results({@Result(javaType=String.class,column="adjust_id",property="adjust_id"),@Result(javaType=String.class,column="total_id",property="total_id"),
		@Result(javaType=String.class,column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="bill_cycle_id",property="bill_cycle_id"),@Result(column="adjust_cycle_id",property="adjust_cycle_id"),
		@Result(column="adjust_type",property="adjust_type"),@Result(column="adjust_fee",property="adjust_fee"),
		@Result(column="adjust_balance",property="adjust_balance"),@Result(column="pay_tag",property="pay_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<JSONObject> getChnlAdjustBillInfo(Integer user_id,String begin_time,String end_time);
}
